package org.aldar.bquest.util;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * 数据库操作
 *
 * @author Longxia
 * @time 2010-10-26
 * @modify Qingfeng 2010-10-26
 */
public class SQLiteHelper extends SQLiteOpenHelper {
    // ===========================================================
    // Constants
    // ===========================================================
    private static final String TAG = "SQLiteHelper";

    private static final String DATABASE_NAME = "tiles.db";//数据库名
    private static final String TABLE_NAME = "saved_game";//表名
    private static final String TABLE_NAME_LEVELS = "levels";//表名
    private static final String TABLE_NAME_ERRORS = "errors";
    private static final int DATABASE_VERSION = 15;//版本号

    // ===========================================================
    // Fields
    // ===========================================================
    private SQLiteDatabase mDB;
    private static Logger LOG = new Logger(SQLiteHelper.class);
    private static SQLiteHelper currentInstance;


    // ===========================================================
    // Methods for/from SuperClass/Interfaces
    // ===========================================================

    public SQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public static SQLiteHelper getCurrentInstance() {
        return currentInstance;
    }

    public static void setCurrentInstance(SQLiteHelper instance) {
        currentInstance = instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table if not exists " + TABLE_NAME + " ("
                + "_id integer primary key autoincrement,"
                + "_name varchar(50),"
                + "_value varchar(50))");
        db.execSQL("create table if not exists " + TABLE_NAME_LEVELS + " ("
                + "id integer primary key autoincrement,"
                + "levelnum integer,"
                + "levelfame integer,"
                + "levelscore integer)");
        db.execSQL("create table if not exists " + TABLE_NAME_ERRORS + " ("
                + "_id integer primary key autoincrement,"
                + "_text varchar(1000))");

        //LOG.i(TAG, "onCreate");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
        //+ newVersion + ", which will destroy all old data");
        db.execSQL("drop table if exists " + TABLE_NAME);
        db.execSQL("drop table if exists " + TABLE_NAME_LEVELS);
        onCreate(db);
        //LOG.i(TAG, "onUpgrade");
    }

    // ===========================================================
    // Methods
    // ===========================================================


    private void insertOrUpdateData(String name, String value) {
        ContentValues values = new ContentValues();
        values.put("_name", name);
        values.put("_value", value);
        mDB.delete(TABLE_NAME, "_name='" + name + "'", null);
        mDB.insert(TABLE_NAME, "_id", values);
    }

    public void insertOrUpdateValue(String name, String value) {
        LOG.i("Inserting value to DB: " + name + " - " + value);
        mDB = getWritableDatabase();
        mDB.beginTransaction();
        try {
            insertOrUpdateData(name, value);
            mDB.setTransactionSuccessful();
        } catch (Exception e) {
            LOG.e(e.getMessage(), e);
        } finally {
            mDB.endTransaction();
            mDB.close();
        }
    }


    public void logException(String text) {
        mDB = getWritableDatabase();
        mDB.beginTransaction();
        try {
            ContentValues values = new ContentValues();
            values.put("_text", new Date() + " " + text.substring(0, 900) + "...");
            mDB.insert(TABLE_NAME_ERRORS, "_id", values);
            mDB.setTransactionSuccessful();
        } catch (Exception e) {
            LOG.e(e.getMessage(), e);
        } finally {
            mDB.endTransaction();
            mDB.close();
        }
    }


    public void deleteValue(String name) {
        LOG.i("Deleting value from DB: " + name);
        mDB = getWritableDatabase();
        mDB.beginTransaction();
        try {
            mDB.delete(TABLE_NAME, "_name='" + name + "'", null);
            mDB.setTransactionSuccessful();
        } catch (Exception e) {
            LOG.e(e.getMessage(), e);
        } finally {
            mDB.endTransaction();
            mDB.close();
        }


    }


    public Map<String, String> queryData() {
        Map<String, String> result = new HashMap<String, String>();
        int i = 0;
        mDB = getReadableDatabase();
        Cursor cursor = mDB.query(TABLE_NAME, null, null, null, null, null, "_name desc", "100");
        int nameIndex = cursor.getColumnIndex("_name");
        int valueIndex = cursor.getColumnIndex("_value");
        for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
            result.put(cursor.getString(nameIndex), cursor.getString(valueIndex));
        }
        cursor.close();
        mDB.close();
        LOG.i("Query result from DB: size = " + result.size() + " content: " + result);
        return result;
    }


    public Map<Integer, Integer[]> queryLevelData() {
        Map<Integer, Integer[]> result = new HashMap<Integer, Integer[]>();
        int i = 0;
        mDB = getReadableDatabase();
        Cursor cursor = mDB.query(TABLE_NAME_LEVELS, null, null, null, null, null, "levelnum asc");
        int levelNumIndex = cursor.getColumnIndex("levelnum");
        int levelScoreIndex = cursor.getColumnIndex("levelscore");
        int levelFameIndex = cursor.getColumnIndex("levelfame");
        for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
            result.put(cursor.getInt(levelNumIndex), new Integer[]{cursor.getInt(levelScoreIndex), cursor.getInt(levelFameIndex)});
        }
        cursor.close();
        mDB.close();
        LOG.i("Query level data result from DB: size = " + result.size() + " content: " + result);
        return result;
    }

    public void saveLevelData(Map<Integer, Integer> levelData) {
        mDB = getWritableDatabase();
        mDB.beginTransaction();
        try {
            for (Map.Entry<Integer, Integer> item : levelData.entrySet()) {
                ContentValues values = new ContentValues();
                values.put("levelnum", item.getKey());
                values.put("levelscore", item.getValue());
                mDB.delete(TABLE_NAME_LEVELS, "levelNum=" + item.getKey() + "", null);
                mDB.insert(TABLE_NAME_LEVELS, "id", values);
            }
            mDB.setTransactionSuccessful();
        } catch (Exception e) {
            LOG.e(e.getMessage(), e);
        } finally {
            mDB.endTransaction();
            mDB.close();
        }
    }

    public void saveLevelData(int levelKey, int levelScore, int levelFame) {
        mDB = getWritableDatabase();
        mDB.beginTransaction();
        try {
            ContentValues values = new ContentValues();
            values.put("levelnum", levelKey);
            values.put("levelscore", levelScore);
            values.put("levelfame", levelFame);
            mDB.delete(TABLE_NAME_LEVELS, "levelNum=" + levelKey + "", null);
            mDB.insert(TABLE_NAME_LEVELS, "id", values);
            mDB.setTransactionSuccessful();
        } catch (Exception e) {
            LOG.e(e.getMessage(), e);
        } finally {
            mDB.endTransaction();
            mDB.close();
        }
    }

    public void resetLevelData() {
        mDB = getWritableDatabase();
        mDB.beginTransaction();
        try {
            mDB.delete(TABLE_NAME_LEVELS, null, null);
            mDB.setTransactionSuccessful();
        } catch (Exception e) {
            LOG.e(e.getMessage(), e);
        } finally {
            mDB.endTransaction();
            mDB.close();
        }

    }


    public String queryAndLogExceptions() {
        LOG.i("Exception report from DB:");
        String result = null;
        int i = 0;
        mDB = getReadableDatabase();
        Cursor cursor = mDB.query(TABLE_NAME_ERRORS, null, null, null, null, null, "_id desc", "10");
        int textIndex = cursor.getColumnIndex("_text");
        for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
            LOG.i(cursor.getString(textIndex));
            if (result == null) {
                result = cursor.getString(textIndex);
            }
        }
        cursor.close();
        mDB.close();
        return result;
    }


}